package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.UserModel;
import com.zr.util.JdbcUtil;

/**
 * 管理员增删改查Dao
 * @author xl
 * 2016年5月24日上午10:47:41
 */
public class MangerSelectDao {

	/**
	 * 查询所有管理员信息
	 * @param pagestart 起始页面数
	 * @param pagerows	每页显示行数
	 * @return 管理员集合
	 */
	public List<UserModel> getAllStudent(int pagestart,int pagerows){
		List<UserModel> list = new ArrayList<>();
		String sql="select sid,sname,scollege,ssex,sage,hid,sphone,saddress from user where spower=2 limit ?,?";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, pagestart);
			pst.setInt(2, pagerows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				UserModel user=new UserModel(rs.getInt("sid"),rs.getString("sname"),rs.getString("scollege"),rs.getString("ssex"),rs.getInt("sage"),rs.getInt("hid"),rs.getString("sphone"),rs.getString("saddress"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closedConnection(con);
		}
		return list;
	}
	/**
	 * 获得管理员人数  分页使用
	 * @return
	 */
	public int getStudentCount(){
		int scount = 0;
		String sql = "select count(*) scount from user where spower=2";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closedConnection(con);
		}
		return  scount;
	}
	/**
	 * 添加管理员信息
	 * @return
	 */
	public boolean getStudentAppend(UserModel user){
		boolean flag=false;
		String sql="insert into user values (?,?,?,?,?,?,?,?,2,?)";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, user.getSid());
			pst.setString(2, user.getSname());
			pst.setInt(3, user.getHid());
			pst.setString(4, user.getScollege());
			pst.setString(5, user.getSsex());
			pst.setInt(6, user.getSage());
			pst.setString(7, user.getSphone());
			pst.setString(8, user.getSaddress());
			pst.setString(9, Integer.toString((user.getSid())));
			if(pst.executeUpdate()>0){
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closedConnection(con);
		}
		return flag;
	}
	/**
	 * 删除管理员
	 * @param sid
	 * @return
	 */
	public boolean deleteStudent(int sid){
		boolean flag=false;
		String sql="delete from user where sid=?";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, sid);
			if(pst.executeUpdate()>0){
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closedConnection(con);
		}
		return flag;
	}
	/**
	 * 修改管理员
	 * @param user 传入管理员
	 * @return
	 */
	public boolean updateStudent(UserModel user){
		boolean flag=false;
		String sql="update user set sname=?,scollege=?,ssex=?,sage=?,saddress=?,sphone=? where sid=?";
		Connection con = null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setString(1, user.getSname());
			pst.setString(2, user.getScollege());
			pst.setString(3, user.getSsex());
			pst.setInt(4, user.getSage());
			pst.setString(5, user.getSaddress());
			pst.setString(6, user.getSphone());
			pst.setInt(7, user.getSid());
			if(pst.executeUpdate()!=0){
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closedConnection(con);
		}
		return flag;
	}

}
